﻿Public Class clsAutoPlantingDB
    Public Function getWorkList() As DataTable

        Dim szSql As String = ""
        Dim dtList As DataTable
        Dim dtDetail As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("LOT_NO")
        dt.Columns.Add("RACK_NO")
        dt.Columns.Add("CHANGE_STATUS")
        dt.Columns.Add("FLOW_NO")
        dt.Columns.Add("IN_COUNT")
        dt.Columns.Add("PRODUCT")
        dt.Columns.Add("PKG_NO")
        dt.Columns.Add("UPDATE_TIME")
        dt.Columns.Add("TYPE")


        szSql = "SELECT DISTINCT LOT_NO,update_time FROM PER.L_SF_WORK_PLANT_RSLT WHERE TYPE<>2 order by update_time"

        dtList = DBacs.ExecuteSql(szSql, -1)

        If IsNothing(dtList) Then
            dtList = New DataTable
        End If

        If IsNothing(dtList) = False Then

            szSql = "SELECT * FROM PER.L_SF_WORK_PLANT_RSLT WHERE TYPE<>2 order by update_time"

            dtDetail = DBacs.ExecuteSql(szSql, -1)

            If IsNothing(dtDetail) Then
                dtDetail = New DataTable
            End If

            If IsNothing(dtDetail) = False Then

                For Each drList As DataRow In dtList.Rows
                    Dim drDetails As DataRow() = dtDetail.Select("LOT_NO='" & drList("LOT_NO") & "'")
                    Dim dr As DataRow = dt.Rows.Add

                    For Each drDetail As DataRow In drDetails
                        dr("LOT_NO") = drDetail("LOT_NO")
                        dr("UPDATE_TIME") = drDetail("UPDATE_TIME")
                        dr("TYPE") = IIf(drDetail("type") = 0, "未着工", "着工中")
                        Select Case drDetail("MNF_CND_TAG")
                            Case "ACCEPTED MS RACK NO"
                                dr("RACK_NO") = drDetail("ACTUAL_MODEL")
                            Case "CHANGE  STATUS"
                                dr("CHANGE_STATUS") = drDetail("ACTUAL_MODEL")
                            Case "FLOW NO"
                                dr("FLOW_NO") = drDetail("ACTUAL_MODEL")
                            Case "OUT COUNT"
                                dr("IN_COUNT") = drDetail("ACTUAL_MODEL")
                            Case "PKG NO"
                                dr("PKG_NO") = drDetail("ACTUAL_MODEL")
                            Case "PRODUCT"
                                dr("PRODUCT") = drDetail("ACTUAL_MODEL")
                        End Select
                    Next

                Next

            End If

        End If
        getWorkList = dt

    End Function

    Public Function changeUpdateTime(lot1 As String, ut1 As String, lot2 As String, ut2 As String) As Integer
        Dim rtn As Integer = -1
        Try

            'トランザクション開始
            DBacs.BeginTransaction()

            Dim szSql As String = ""
            szSql &= " update PER.L_SF_WORK_PLANT_RSLT"
            szSql &= " set update_time= ? "
            szSql &= " WHERE lot_no= ? "


            DBacs.ExecuteUpdateSql(szSql, -1, ut2, lot1)
            DBacs.ExecuteUpdateSql(szSql, -1, ut1, lot2)
            DBacs.CommitTransaction()
            Return 1
        Catch ex As Exception
            DBacs.RollbackTransaction()

            Return -1
        End Try
    End Function
End Class
